
package com.voicecheck.provider;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.aris.voicechecking.R;
import com.voicecheck.model.DayLesson;
import com.voicecheck.model.HistoryModel;
import com.voicecheck.util.StringUtil;
import com.voicecheck.v2.model.HistoryLessonModel;
import com.voicecheck.v2.model.LessonModel;
import com.voicecheck.v2.model.SentencesModel;
import com.voicecheck.v2.model.UrlModel;

public class DataProviderV2 extends SQLiteOpenHelper {

    protected Context ct;

    protected static String DATABASE_NAME = "databasev2.db";

    protected static int DATABASE_VERSION = 2;

    protected static final String SENTENCES_DB = "tb_sentences";

    protected static final String LESSON_DB = "tb_lesson";

    protected static final String HISTORY_DB = "tb_history";

    protected static final String URL_DB = "tb_url";

    protected static final String ID = "id";

    protected static final String WEEK = "week";

    protected static final String DAY = "day";

    protected static final String LESSON = "lesson";

    protected static final String SE_SENTENCES = "sentence";

    protected static final String CHARACTER = "character";

    protected static final String QUESTION_CONTENT = "question_content";

    protected static final String RECOGNIZE = "recognize";

    protected static final String AUDIO_NAME = "audio_name";

    protected static final String URL = "url";

    protected static final String SUBJECT = "subject";

    protected static final String DAY_SUBJECT = "day_subject";

    protected static final String COVER_IMAGE = "cover_image";

    protected static final String DONE = "done";

    protected static final String LAST_DONE = "last_done";

    protected static final String DATE = "date";

    protected static final String DIR = "dir";

    protected SharedPreferences sharedpreferences;

    protected static final String SENTENCES_DB_CREATE = "create table " + SENTENCES_DB + " ( " + ID
            + " integer primary key autoincrement, " + WEEK + " integer, " + DAY + " integer, "
            + LESSON + " integer, " + SE_SENTENCES + " integer, " + CHARACTER + " text, "
            + QUESTION_CONTENT + " text, " + RECOGNIZE + " integer, " + AUDIO_NAME + " text, "
            + URL + " text )";

    public static final String LESSON_DB_CREATE = "create table " + LESSON_DB + " ( " + ID
            + " integer primary key autoincrement, " + WEEK + " integer, " + DAY + " integer, "
            + LESSON + " integer, " + SUBJECT + " text, " + DAY_SUBJECT + " text, " + COVER_IMAGE
            + " text, " + DONE + " integer, " + URL + " text)";

    public static final String HISTORY_DB_CREATE = "create table " + HISTORY_DB + " ( " + DATE
            + " text, " + WEEK + " integer, " + DAY + " integer, " + LESSON + " integer, "
            + LAST_DONE + " integer, " + URL + " text )";

    public static final String URL_DB_CREATE = "create table " + URL_DB + " ( " + ID
            + " integer primary key autoincrement, " + URL + " text, " + DIR + " text)";

    public DataProviderV2(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);

        ct = context;
        sharedpreferences = ct.getSharedPreferences(ct.getString(R.string.user_data),
                Context.MODE_PRIVATE);
    }

    public DataProviderV2(Context context, String name, CursorFactory factory, int version) {
        super(context, name, factory, version);

        ct = context;
        sharedpreferences = ct.getSharedPreferences(ct.getString(R.string.user_data),
                Context.MODE_PRIVATE);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL(SENTENCES_DB_CREATE);
        db.execSQL(LESSON_DB_CREATE);
        db.execSQL(HISTORY_DB_CREATE);
        db.execSQL(URL_DB_CREATE);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(DataProviderV2.class.getName(), "Upgrading database from version " + oldVersion
                + " to " + newVersion + ", which will destroy all old data");

        db.execSQL("DROP TABLE IF EXISTS " + SENTENCES_DB_CREATE);
        db.execSQL("DROP TABLE IF EXISTS " + LESSON_DB_CREATE);
        db.execSQL("DROP TABLE IF EXISTS " + HISTORY_DB_CREATE);
        db.execSQL("DROP TABLE IF EXISTS " + URL_DB_CREATE);

        onCreate(db);
    }

    public Boolean insertSentences(SentencesModel sentences) {

        if (retrieveSentencesBySentence(sentences.getWeek(), sentences.getDay(),
                sentences.getLesson(), sentences.getSentences()).size() > 0) {
            return false;
        }

        long urlID = sharedpreferences.getLong(ct.getString(R.string.user_data_connect_link), 1);

        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(WEEK, sentences.getWeek());
        values.put(DAY, sentences.getDay());
        values.put(LESSON, sentences.getLesson());
        values.put(CHARACTER, sentences.getCharacter());
        values.put(QUESTION_CONTENT, sentences.getQuestion_content());
        values.put(RECOGNIZE, sentences.getRecognize());
        values.put(AUDIO_NAME, sentences.getAudio_name());
        values.put(SE_SENTENCES, sentences.getSentences());
        values.put(URL, urlID);

        long insertId = db.insert(SENTENCES_DB, null, values);

        // db.close();

        if (insertId == -1)
            return false;
        return true;
    }

    public List<SentencesModel> retrieveSentencesBySentence(int week, int day, int lesson,
            int sequence) {
        long urlID = sharedpreferences.getLong(ct.getString(R.string.user_data_connect_link), 1);

        List<SentencesModel> sentences = new ArrayList<SentencesModel>();

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.query(
                SENTENCES_DB,
                new String[] {
                        ID, WEEK, DAY, LESSON, CHARACTER, QUESTION_CONTENT, RECOGNIZE, AUDIO_NAME
                },
                WEEK + "=? AND " + DAY + "=? AND " + LESSON + "=? AND " + SE_SENTENCES + "=? AND "
                        + URL + "=? ",
                        new String[] {
                        String.valueOf(week), String.valueOf(day), String.valueOf(lesson),
                        String.valueOf(sequence), String.valueOf(urlID)
                }, null, null, null, null);

        if (cursor.moveToFirst()) {
            do {

                SentencesModel sentence = new SentencesModel();
                sentence.setCharacter(cursor.getString(4));
                sentence.setQuestion_content(cursor.getString(5));
                sentence.setRecognize(cursor.getString(6));
                sentence.setAudio_name(cursor.getString(7));

                sentences.add(sentence);

            } while (cursor.moveToNext());
        }

        cursor.close();
        // db.close();

        return sentences;
    }

    public List<SentencesModel> retrieveSentences(int week, int day, int lesson) {

        long urlID = sharedpreferences.getLong(ct.getString(R.string.user_data_connect_link), 1);

        List<SentencesModel> sentences = new ArrayList<SentencesModel>();

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.query(
                SENTENCES_DB,
                new String[] {
                        ID, WEEK, DAY, LESSON, CHARACTER, QUESTION_CONTENT, RECOGNIZE, AUDIO_NAME
                },
                WEEK + "=? AND " + DAY + "=? AND " + LESSON + "=? AND " + URL + "=? ",
                new String[] {
                        String.valueOf(week), String.valueOf(day), String.valueOf(lesson),
                        String.valueOf(urlID)
                }, null, null, null, null);

        if (cursor.moveToFirst()) {
            do {

                SentencesModel sentence = new SentencesModel();

                sentence.setCharacter(cursor.getString(4));
                sentence.setQuestion_content(cursor.getString(5));
                sentence.setRecognize(cursor.getString(6));
                sentence.setAudio_name(cursor.getString(7));

                sentences.add(sentence);

            } while (cursor.moveToNext());
        }

        cursor.close();
        // db.close();

        return sentences;

    }

    public int getWeekNumber() {
        long urlID = sharedpreferences.getLong(ct.getString(R.string.user_data_connect_link), 1);

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery("SELECT  WEEK FROM " + LESSON_DB + "  where " + URL + " = "
                + urlID + " GROUP BY " + WEEK, null);

        int count = 0;
        if (cursor.moveToFirst()) {
            do {
                count++;

            } while (cursor.moveToNext());
        }
        cursor.close();
        // db.close();
        return count;
    }

    public int getDayNumber(int week) {
        long urlID = sharedpreferences.getLong(ct.getString(R.string.user_data_connect_link), 1);

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery("SELECT  DAY FROM " + LESSON_DB + "  where " + WEEK + " = "
                + week + " AND " + URL + " = " + urlID + " GROUP BY " + DAY, null);

        int count = 0;
        if (cursor.moveToFirst()) {
            do {
                count++;

            } while (cursor.moveToNext());
        }
        cursor.close();
        // db.close();
        return count;
    }

    public Boolean insertLesson(LessonModel lesson) {

        if (retrieveLessonByLesson(lesson.getWeek(), lesson.getDay(), lesson.getLesson()).size() > 0) {
            return false;
        }

        long urlID = sharedpreferences.getLong(ct.getString(R.string.user_data_connect_link), 1);

        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(WEEK, lesson.getWeek());
        values.put(DAY, lesson.getDay());
        values.put(LESSON, lesson.getLesson());
        values.put(SUBJECT, lesson.getSubject());
        values.put(DAY_SUBJECT, lesson.getDay_subject());
        values.put(COVER_IMAGE, lesson.getCover_image());
        values.put(DONE, lesson.getDone());
        values.put(DAY_SUBJECT, lesson.getDay_subject());
        values.put(URL, urlID);

        long insertId = db.insert(LESSON_DB, null, values);

        // db.close();

        if (insertId == -1)
            return false;
        return true;

    }

    public Boolean updateLessonDone(long id) {

        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(DONE, 1);
        int val = db.update(LESSON_DB, values, ID + " = ? ", new String[] {
                Long.toString(id)
        });

        if (val == 0)
            return false;

        return true;
    }

    public List<LessonModel> retrieveLessonByDay(int week, int day) {

        long urlID = sharedpreferences.getLong(ct.getString(R.string.user_data_connect_link), 1);

        List<LessonModel> lessons = new ArrayList<LessonModel>();

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.query(LESSON_DB, new String[] {
                ID, WEEK, DAY, LESSON, SUBJECT, DAY_SUBJECT, COVER_IMAGE, DONE, URL
        }, WEEK + "=? AND " + DAY + "=? AND " + URL + "=? ", new String[] {
                String.valueOf(week), String.valueOf(day), String.valueOf(urlID)
        }, null, null, null, null);

        int i = 1;
        if (cursor.moveToFirst()) {
            do {

                LessonModel lesson = new LessonModel();

                lesson.setId(cursor.getLong(0));
                lesson.setWeek(cursor.getInt(1));
                lesson.setDay(cursor.getInt(2));
                lesson.setLesson(cursor.getInt(3));
                lesson.setSubject(cursor.getString(4));
                lesson.setDay_subject(cursor.getString(5));
                lesson.setCover_image(cursor.getString(6));
                lesson.setDone(cursor.getInt(7));
                lesson.setSentences(retrieveSentences(week, day, i));
                lesson.setAnswered(false);
                lesson.setAnswered_str("");
                lesson.setLast_done(0);

                lessons.add(lesson);
                i++;

            } while (cursor.moveToNext());
        }

        cursor.close();
        // db.close();

        return lessons;
    }

    public List<LessonModel> retrieveLessonByLesson(int week, int day, int se) {

        long urlID = sharedpreferences.getLong(ct.getString(R.string.user_data_connect_link), 1);

        List<LessonModel> lessons = new ArrayList<LessonModel>();

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.query(
                LESSON_DB,
                new String[] {
                        ID, WEEK, DAY, LESSON, SUBJECT, DAY_SUBJECT, COVER_IMAGE, DONE, URL
                },
                WEEK + "=? AND " + DAY + "=? AND " + LESSON + "=? AND " + URL + "=? ",
                new String[] {
                        String.valueOf(week), String.valueOf(day), String.valueOf(se),
                        String.valueOf(urlID)
                }, null, null, null, null);

        if (cursor.moveToFirst()) {
            do {

                LessonModel lesson = new LessonModel();

                lesson.setId(cursor.getLong(0));
                lesson.setWeek(cursor.getInt(1));
                lesson.setDay(cursor.getInt(2));
                lesson.setLesson(cursor.getInt(3));
                lesson.setSubject(cursor.getString(4));
                lesson.setDay_subject(cursor.getString(5));
                lesson.setCover_image(cursor.getString(6));
                lesson.setDone(cursor.getInt(7));
                lesson.setSentences(retrieveSentences(week, day, se));
                lesson.setAnswered(false);
                lesson.setAnswered_str("");
                lesson.setLast_done(0);

                lessons.add(lesson);

            } while (cursor.moveToNext());
        }

        cursor.close();
        // db.close();

        return lessons;
    }

    public Boolean insertHistoryLesson(int week, int day, int lesson, int done) {

        long urlID = sharedpreferences.getLong(ct.getString(R.string.user_data_connect_link), 1);

        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(DATE, StringUtil.getDate());
        values.put(WEEK, week);
        values.put(DAY, day);
        values.put(LESSON, lesson);
        values.put(LAST_DONE, done);
        values.put(URL, urlID);

        long insertId = db.insert(HISTORY_DB, null, values);

        // db.close();

        if (insertId == -1)
            return false;

        return true;
    }

    public Boolean updateHistoryLessonLastDone(int week, int day, int lesson) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        long urlID = sharedpreferences.getLong(ct.getString(R.string.user_data_connect_link), 1);

        values.put(LAST_DONE, 1);
        int val = db.update(HISTORY_DB, values, DATE + "=? AND " + WEEK + " = ? AND " + DAY
                + " =? AND " + LESSON + " =? AND " + URL + " =?",
                new String[] {
                StringUtil.getDate(), Integer.toString(week), Integer.toString(day),
                Integer.toString(lesson), String.valueOf(urlID)
        });

        if (val == 0) {
            insertHistoryLesson(week, day, lesson, 1);
        }

        return true;
    }

    public Boolean updateHistoryLessonLastFail(int week, int day, int lesson) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        String date = StringUtil.getDate();

        long urlID = sharedpreferences.getLong(ct.getString(R.string.user_data_connect_link), 1);

        values.put(LAST_DONE, 0);
        int val = db.update(HISTORY_DB, values, DATE + "=? AND " + WEEK + " = ? AND " + DAY
                + " =? AND " + LESSON + " =? AND " + URL + " =?", new String[] {
                date, Integer.toString(week), Integer.toString(day), Integer.toString(lesson),
                String.valueOf(urlID)
        });

        if (val == 0) {
            insertHistoryLesson(week, day, lesson, 0);
        }

        return true;
    }

    public List<HistoryModel> retrieveHistoryLesson() {
        List<HistoryModel> lhm = new ArrayList<HistoryModel>();
        long urlID = sharedpreferences.getLong(ct.getString(R.string.user_data_connect_link), 1);

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery("SELECT t." + DATE + " , COUNT(t." + DATE + ") " + "FROM "
                + HISTORY_DB + " t  where t." + URL + " = " + urlID + " GROUP BY " + DATE
                + " ORDER BY " + DATE + " DESC", null);

        if (cursor.moveToFirst()) {
            do {
                HistoryModel hm = new HistoryModel();
                hm.setDate(cursor.getString(0));
                hm.setQuestion_count(cursor.getInt(1));
                hm.setPass_count(retrieveDoneFromDate(cursor.getString(0)));
                lhm.add(hm);

            } while (cursor.moveToNext());
        }
        cursor.close();
        // db.close();

        while (lhm.size() > 30) {
            int index = lhm.size() - 1;
            String hmodeldate = lhm.get(index).getDate();
            lhm.remove(index);

            try {
                db.delete(HISTORY_DB, DATE + " = " + hmodeldate, null);
            } catch (Exception e) {

            }
        }

        return lhm;
    }

    public List<HistoryLessonModel> retrieveHistoryLessonByDate(String date) {
        long urlID = sharedpreferences.getLong(ct.getString(R.string.user_data_connect_link), 1);

        List<HistoryLessonModel> lessons = new ArrayList<HistoryLessonModel>();

        SQLiteDatabase db = this.getWritableDatabase();

        String orderBy = WEEK + " ASC, " + DAY + " ASC, " + LESSON + " ASC";

        Cursor cursor = db.query(HISTORY_DB, new String[] {
                DATE, WEEK, DAY, LESSON, LAST_DONE
        }, DATE + "=? AND " + URL + "=? ", new String[] {
                date, String.valueOf(urlID)
        }, null, null, orderBy, null);

        if (cursor.moveToFirst()) {
            do {

                HistoryLessonModel lesson = new HistoryLessonModel();

                lesson.setDate(cursor.getString(0));
                lesson.setWeek(cursor.getInt(1));
                lesson.setDay(cursor.getInt(2));
                lesson.setLesson(cursor.getInt(3));
                lesson.setLast_done(cursor.getInt(4));
                lessons.add(lesson);

            } while (cursor.moveToNext());
        }

        cursor.close();
        // db.close();

        return lessons;
    }

    public int retrieveDoneFromDate(String date) {

        SQLiteDatabase db = this.getWritableDatabase();

        long urlID = sharedpreferences.getLong(ct.getString(R.string.user_data_connect_link), 1);

        Cursor cursor = db.query(HISTORY_DB, new String[] {
                LAST_DONE
        }, DATE + "=? AND " + URL + "=?", new String[] {
                String.valueOf(date), String.valueOf(urlID)
        }, null, null, null, null);

        int total = 0;
        if (cursor.moveToFirst()) {
            do {

                total += cursor.getInt(0);

            } while (cursor.moveToNext());
        }

        cursor.close();
        // db.close();

        return total;
    }

    public long insertUrlTable(String url) {

        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(URL, url);

        String sourcePath = ct.getApplicationInfo().dataDir + "/package/";
        ;

        String dirPath = "";
        for (int i = 1; i <= 1000; i++) {

            File file = new File(sourcePath + "/package" + i + "/");

            if (!file.exists()) {
                file.mkdirs();
                dirPath = file.getAbsolutePath();
                break;
            }
        }

        values.put(DIR, dirPath);

        long insertId = db.insert(URL_DB, null, values);

        // db.close();

        return insertId;

    }

    public Boolean updateUrlTable(String url) {

        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(URL, url);
        int val = db.update(URL_DB, values, URL + " = ? ", new String[] {
                url
        });

        if (val == 0) {
            long valID = insertUrlTable(url);

            if (valID != -1)
                return true;
            else
                return false;
        }

        return true;

    }

    public UrlModel retrieveURLByID(long id) {
        SQLiteDatabase db = this.getWritableDatabase();
        UrlModel model = new UrlModel();

        Cursor cursor = db.query(URL_DB, new String[] {
                ID, URL, DIR
        }, ID + "=? ", new String[] {
                String.valueOf(id)
        }, null, null, null, null);

        if (cursor.moveToFirst()) {
            do {

                model.setId(id);
                model.setUrl(cursor.getString(1));
                model.setDir(cursor.getString(2));

                break;

            } while (cursor.moveToNext());
        }

        cursor.close();
        // db.close();

        return model;
    }

    public boolean deleteURL(long id) {

        Log.d("delete url", "url with id = " + id);
        SQLiteDatabase db = this.getWritableDatabase();

        UrlModel model = retrieveURLByID(id);

        File f = new File(model.getDir());
        if (f.exists())
            f.delete();

        Boolean deleted = db.delete(URL_DB, ID + "=" + id, null) > 0;

        return deleted;
    }

    public UrlModel retrieveURLByURL(String url) {
        SQLiteDatabase db = this.getWritableDatabase();
        UrlModel model;

        Cursor cursor = db.query(URL_DB, new String[] {
                ID, URL, DIR
        }, URL + "=? ", new String[] {
                String.valueOf(url)
        }, null, null, null, null);

        if (cursor.moveToFirst()) {
            do {

                model = new UrlModel();
                model.setId(cursor.getLong(0));
                model.setUrl(cursor.getString(1));
                model.setDir(cursor.getString(2));

                break;

            } while (cursor.moveToNext());
        } else {
            model = null;
        }

        cursor.close();
        // db.close();

        return model;
    }

    public double calculatorPoint() {
        int total = 0;
        try {
            long urlID = sharedpreferences
                    .getLong(ct.getString(R.string.user_data_connect_link), 1);

            String selectQuery = "SELECT  * FROM " + LESSON_DB + " where " + URL + " = " + urlID;

            SQLiteDatabase db = this.getWritableDatabase();
            Cursor cursor = db.rawQuery(selectQuery, null);

            // looping through all rows and adding to list
            if (cursor.moveToFirst()) {
                do {
                    total += cursor.getInt(7);
                } while (cursor.moveToNext());
            }
        } catch (Exception e) {
        }

        // db.close();

        return total * 1.0 / 192 * 100;
    }

    public DayLesson retrieveInfomationOfDayLesson(int week, int day) {

        DayLesson daylesson = new DayLesson();
        List<LessonModel> listLesson = retrieveLessonByDay(week, day);

        if (listLesson.size() == 0)
            return null;

        int index = listLesson.get(0).getSubject().indexOf(':');

        Log.d("contain", index + "");
        String[] subject = listLesson.get(0).getSubject().split("\\: ");

        if (subject.length > 2) {
            daylesson.setTitle(subject[1]);
        } else {
            daylesson.setTitle(listLesson.get(0).getDay_subject());
        }

        int total = 0;
        for (int i = 0; i < listLesson.size(); i++) {
            total += listLesson.get(i).getDone();
        }

        daylesson.setNumber_question_available(total);

        return daylesson;
    }

}
